with cus as (
  select 
       code
      ,name
      ,attribute_id
      ,create_time
  from
  jms_dim.dim_lmdm_sys_customer
),
network as (
select 
        code
        ,name
        ,franchisee_code
        ,franchisee_name
        ,agent_code
        ,agent_name
        ,provider_id
        ,city_id
        ,area_id
        ,provider_desc
        ,city_desc
        ,area_desc
from jms_dim.dim_sys_network_detail_dt
where dt = '{{ execution_date | cst_ds }}'
),
way as (
select waybill_no
        ,input_time 
        ,pick_network_code
        ,customer_code
        ,dt
from jms_ods.yl_oms_oms_waybill
where dt = '{{ execution_date | cst_ds }}' 
and to_date(input_time) = '{{ execution_date | cst_ds }}'
and is_delete = 1
and is_void = 0 
)
insert overwrite table jms_dwd.dwd_customer_detail_dt partition(dt)
select                              
      way.input_time               as input_time
     ,date(way.input_time)         as input_date
     ,way.waybill_no               as waybill_no
     ,cus.name                     as customer_name
     ,way.customer_code            as customer_code
     ,cus.attribute_id             as attribute_id
     ,cus.create_time              as create_time
     ,network.franchisee_code      as franchisee_code
     ,network.franchisee_name      as franchisee_name
     ,network.agent_code           as agent_code
     ,network.agent_name           as agent_name
     ,network.provider_id          as provider_id
     ,network.city_id              as city_id
     ,network.area_id              as area_id
     ,network.provider_desc        as provider_desc
     ,network.city_desc            as city_desc
     ,network.area_desc            as area_desc
     ,pick_network_code            as pick_network_code
     ,network.name                 as pick_network_name
     ,to_date(input_time)          as dt
from way 
left join cus on coalesce(way.customer_code,way.waybill_no) = cus.code
left join network on coalesce(way.pick_network_code,way.waybill_no) = network.code
  distribute by dt,pmod(hash(rand()),15);